﻿using QuanLyCongDan.DTO;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;

namespace DAL
{
    public class DAL_DonVi
    {
        /// <summary>
        /// Insert this object DonVi into database.
        /// </summary>
        /// <param name="donViInput">The inserting DonVi object</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertDonVi(DTO_DonVi donViInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO DonVi " +
                "([MaDV], [TenDV]) " +
                "VALUES " +
                "(@MA, @TEN)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(donViInput.MaDonVi) ? string.Empty : donViInput.MaDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TEN";
            param.Value = string.IsNullOrEmpty(donViInput.TenDonVi) ? string.Empty : donViInput.TenDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Update this object DonVi from database.
        /// </summary>
        /// <param name="donViInput">The updating DonVi object</param>
        /// <returns>True if update successfully, false if not</returns>
        public static bool UpdateDonVi(DTO_DonVi donViInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "UPDATE DonVi " +
                "SET [TenDV] = @TEN " +
                "WHERE [MaDV] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@TEN";
            param.Value = string.IsNullOrEmpty(donViInput.TenDonVi) ? string.Empty : donViInput.TenDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(donViInput.MaDonVi) ? string.Empty : donViInput.MaDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);



            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static bool UpdateDonVi(string maDonVi, int dongDau, int dongCuoi, string filePath)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "UPDATE DonVi " +
                "SET [DongDau] = @DongDau, [DongCuoi] = @DongCuoi, [FileMau] = @FileMau " +
                "WHERE [MaDV] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@DongDau";
            param.Value = dongDau;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DongCuoi";
            param.Value = dongCuoi;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@FileMau";
            param.Value = string.IsNullOrEmpty(filePath) ? string.Empty : filePath;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(maDonVi) ? string.Empty : maDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }


        /// <summary>
        /// Delete this object DonVi from database.
        /// </summary>
        /// <param name="donViInput">The deleting DonVi object</param>
        /// <returns>True if delete successfully, false if not</returns>
        public static bool DeleteDonVi(DTO_DonVi donViInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            comm.CommandText =
                "DELETE DonVi " +
                "WHERE [MaDV] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(donViInput.MaDonVi) ? string.Empty : donViInput.MaDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Retrieves all DonVi in database.
        /// </summary>
        /// <returns>List of all DonVi.</returns>
        public static List<DTO_DonVi> GetListDonVi()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT * FROM DonVi";

            List<DTO_DonVi> donViList = new List<DTO_DonVi>();
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonVi curDonVi = new DTO_DonVi();
                curDonVi.MaDonVi = (dr["MaDV"] == System.DBNull.Value) ? string.Empty : (string)dr["MaDV"]; ;
                curDonVi.TenDonVi = (dr["TenDV"] == System.DBNull.Value) ? string.Empty : (string)dr["TenDV"];

                donViList.Add(curDonVi);
            }
            return donViList;
        }

        /// <summary>
        /// Retrieves DonVi by ID in database.
        /// </summary>
        /// <returns>DonVi with given ID.</returns>
        public static DTO_DonVi GetDonVi(string MaDV)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT * FROM DonVi WHERE [MaDV] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(MaDV) ? string.Empty : MaDV;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            DataRow dr = dt.Rows[0];
            DTO_DonVi curDonVi = new DTO_DonVi();
            curDonVi.MaDonVi = (dr["MaDV"] == System.DBNull.Value) ? string.Empty : (string)dr["MaDV"]; ;
            curDonVi.TenDonVi = (dr["TenDV"] == System.DBNull.Value) ? string.Empty : (string)dr["TenDV"];
            return curDonVi;
        }

        public static int GetTemplateDonViDongDau(string MaDV)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT DongDau FROM DonVi WHERE [MaDV] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = string.IsNullOrEmpty(MaDV) ? string.Empty : MaDV;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            DataRow dr = dt.Rows[0];
            return (int)dr["DongDau"];;
        }

        /// <summary>
        /// Update DonVi table
        /// </summary>
        public static void UpdateDonViTable(DataSet ds)
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select * From DonVi";
            //B4: Thuc thi chuoi strSQL
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
            da.Update(ds.Tables[0]);
            cn.Close();
        }

        /// <summary>
        /// Get DonVi table
        /// </summary>
        /// <returns>The dataset of DonVi table</returns>
        public static DataSet GetDonViTable()
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select * From DonVi";
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        public static bool InsertFieldDonVi(string MaDonVi, DTO_Field listField)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO DonVi_Field " +
                "([MaDonVi], [MaField], [Value]) " +
                "VALUES " +
                "(@MaDV, @MaField, @Value)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MaDV";
            param.Value = string.IsNullOrEmpty(MaDonVi) ? string.Empty : MaDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@MaField";
            param.Value = listField.MaField;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Value";
            param.Value = listField.Value;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static void InsertFieldDonViList(string MaDonVi, List<DTO_Field> listField)
        {
            foreach (DTO_Field curField in listField)
            {
                InsertFieldDonVi(MaDonVi, curField);
            }            
        }
    }
}
